from sqlalchemy import create_engine, text



# SQL插入语句
sql_insert_statements = [
    "INSERT INTO sys_depts VALUES (100,  0,   '0',          '集团总公司',   0, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (101,  100, '0,100',      '深圳分公司', 1, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (102,  100, '0,100',      '长沙分公司', 2, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (103,  101, '0,100,101',  '研发部门',   1, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (104,  101, '0,100,101',  '市场部门',   2, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (105,  101, '0,100,101',  '测试部门',   3, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (106,  101, '0,100,101',  '财务部门',   4, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (107,  101, '0,100,101',  '运维部门',   5, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (108,  102, '0,100,102',  '市场部门',   1, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "INSERT INTO sys_depts VALUES (109,  102, '0,100,102',  '财务部门',   2, '年糕', '15888888888', 'niangao@qq.com', '0', '0', 'admin', SYSDATE(), '', NULL, NULL);",
    "insert into sys_users values(1,  103, 'admin',   '超级管理员', '00', 'niangao@163.com', '15888888888', '1', '', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', sysdate(), 'admin', sysdate(), '', null, null, '管理员');",
    "insert into sys_users values(2,  105, 'niangao', '年糕', 			'00', 'niangao@qq.com',  '15666666666', '1', '', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', sysdate(), 'admin', sysdate(), '', null, null, '测试员');",
    "insert into sys_roles values('1', '超级管理员',  'admin',  1, 1, 1, 1, '0', '0', 'admin', sysdate(), '', null, null ,'超级管理员');",
    "insert into sys_roles values('2', '普通角色',    'common', 2, 2, 1, 1, '0', '0', 'admin', sysdate(), '', null, null, '普通角色');",
    "insert into sys_user_roles values ('1', '1');",
    "insert into sys_user_roles values ('2', '2');",
    "insert into sys_role_depts values ('2', '100');",
    "insert into sys_role_depts values ('2', '101');",
    "insert into sys_role_depts values ('2', '105');",
    "insert into sys_user_posts values ('1', '1');",
    "insert into sys_user_posts values ('2', '2');"


]

# 创建数据库连接引擎
DATABASE_URL = "mysql+pymysql://root:rapidBuilder!@120.25.63.187:6740/rapid"

engine = create_engine(DATABASE_URL)

# 执行插入操作
with engine.connect() as connection:
    for sql in sql_insert_statements:
        connection.execute(text(sql))

print("数据插入成功")
